package com.fzh.Zhanye.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.fzh.Zhanye.model.Type1;
import com.fzh.Zhanye.model.Type2;
import com.fzh.Zhanye.util.Constants;

public class Type2Dao {
	public static void main(String[] args) {
		Type2Dao dao=new Type2Dao();
		try {
			List<Type2> list=dao.findByType1("2");
			for(Type2 type2:list){
				System.out.println(type2);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}
	}

	public List<Type2> findByType1(String t1Id) throws SQLException {
		
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;
		List<Type2> list=new ArrayList<>();
		try {
			conn=DBConnection.getConnection();
			String sql="SELECT T2_ID,`NAME`,CREATOR,CREATOR_TIME,"
					+ "OPDATOR,OPDTE_TIME,REMARK FROM type2 WHERE T1_ID=?";		
			pstm=conn.prepareStatement(sql);
			pstm.setString(1, t1Id);
			rs=pstm.executeQuery();
			while(rs.next()){
				
				Type2 type2=new Type2(rs.getString("CREATOR"), rs.getDate("CREATOR_TIME"), rs.getString("OPDATOR"),
						rs.getDate("OPDTE_TIME"), rs.getString("REMARK"), rs.getString("T2_ID"), rs.getString("NAME"), null);
				list.add(type2);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(rs!=null){
				rs.close();
			}
			if(pstm!=null){
				pstm.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		
		return list;
	}
	public List<Type2> findByPage(int page) throws SQLException {
		Connection conn=null;
		PreparedStatement pstmt=null;
		ResultSet rs=null;
		List<Type2> list=new ArrayList<>();
		try {
			conn=DBConnection.getConnection();
			String sql="SELECT t1.T1_ID,t2.T2_ID,t1.`NAME`,t2.`NAME`,t1.CREATOR,t1.CREATOR_TIME,t1.OPDATOR,t1.OPDTE_TIME FROM type2 t2 "
					+ "INNER JOIN type1 t1 ON t2.T1_ID=t1.T1_ID  LIMIT ?,?";
			pstmt=conn.prepareStatement(sql);
			pstmt.setInt(1, (page-1)*Constants.PAGE_SIZE);
			pstmt.setInt(2, Constants.PAGE_SIZE);
			rs=pstmt.executeQuery();
			while(rs.next()){
				Type1 type1=new Type1(null, null, null, null, null, rs.getString("T1_ID"), rs.getString("Name"));
				Type2 type2=new Type2(rs.getString("CREATOR"), rs.getDate("CREATOR_TIME"), rs.getString("OPDATOR"), 
						rs.getDate("OPDTE_TIME"), null, rs.getString("T2_ID"), rs.getString("NAME"), type1);
				list.add(type2);
			}
			
		} catch (Exception e) {
			e.printStackTrace();
		}finally{
			if(pstmt!=null){
				pstmt.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return list;
	}

	public int findCount() throws SQLException {
		Connection conn=null;
		PreparedStatement pstm=null;
		ResultSet rs=null;

		try {
			conn=DBConnection.getConnection();
			String sql="SELECT COUNT(T2_ID) FROM type2";					
			pstm=conn.prepareStatement(sql);
			rs=pstm.executeQuery();
			while(rs.next()){
				return rs.getInt(1);
			}
		} catch (Exception e) {
			e.printStackTrace();
		}finally {
			if(rs!=null){
				rs.close();
			}
			if(pstm!=null){
				pstm.close();
			}
			if(conn!=null){
				conn.close();
			}
		}
		return 0;
		
	}
	

}
